package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.Beans.Albums;

@Repository (value="albumdao")
public class AlbumDAOImpl implements AlbumDAO{
	@Autowired
	private JdbcTemplate jt;
	@Autowired
	private TagDAOImpl tagdao;

	@Override
	public int add(final Albums album) {
		// TODO Auto-generated method stub
		int id=0;
		if(album!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_ADD(?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, album.getUserID());
							stm.setString(2, album.getName());
							stm.setString(3, album.getDescription());
							stm.setString(4, album.getCharacter());
							stm.setString(5, album.getCostume());
							stm.setString(6, album.getPhotographer());
							stm.setString(7, album.getType());
							stm.registerOutParameter(8, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(8);
							obj[1]=rs;
							return obj;
						}});
			id=((Integer) obj[0]).intValue();
			tagdao.updateTagByAlbum(album.getTags(), id);
		}
		return id;
	}

	@Override
	public boolean delete(final int aid) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(aid>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_DELETE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, aid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
			sucess=true;
		}
		return sucess;
	}

	@Override
	public boolean edit(final Albums album, final int aid) {
		// TODO Auto-generated method stub
		if (aid<0)
			return false;
		boolean sucess=false;
		if(album!=null)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_EDIT(?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, album.getUserID());
							stm.setString(2, album.getName());
							stm.setString(3, album.getDescription());
							stm.setString(4, album.getCharacter());
							stm.setString(5, album.getCostume());
							stm.setString(6, album.getPhotographer());
							stm.setString(7, album.getType());
							stm.setInt(8, aid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
			tagdao.updateTagByAlbum(album.getTags(), aid);
			sucess=true;
		}
		return sucess;
	}

	@Override
	public Albums get(final int id) {
		// TODO Auto-generated method stub
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_GET(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							if (rs.next())
							{
								Albums album=new Albums();
								album.setAdded(rs.getTimestamp("adddate"));
								album.setCharacter(rs.getString("character"));
								album.setCommented(rs.getInt("commented"));
								album.setCostume(rs.getString("costume"));
								album.setDescription(rs.getString("description"));
								album.setId(rs.getInt("id"));
								album.setLastuploaded(rs.getTimestamp("lastuploaded"));
								album.setLastviewed(rs.getTimestamp("lastviewed"));
								album.setName(rs.getString("name"));
								album.setPhotographer(rs.getString("photographer"));
								album.setType(rs.getString("type"));
								album.setUserID(rs.getInt("userid"));
								album.setUserName(rs.getString("username"));
								album.setViewed(rs.getInt("viewed"));
								obj[1]=album;
							}
							return obj;
						}});
			if(obj[1]!=null)
			{
				Albums album=(Albums) obj[1];
				album.setTags(tagdao.getByPhoto(id));
				return album;
			}
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getAll() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL ALBUM_GETALL();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> competitions = new ArrayList<Integer>();
							while (rs.next())
							{
								competitions.add(rs.getInt("id"));
							}
							if (competitions.size()>0)
								obj[1]=competitions;
							return obj;
						}});
		if(obj[1]!=null)
			return (List<Integer>) obj[1];
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getLastUpload() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL ALBUM_GETLASTUPLOADED();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> competitions = new ArrayList<Integer>();
							while (rs.next())
							{
								competitions.add(rs.getInt("id"));
							}
							if (competitions.size()>0)
								obj[1]=competitions;
							return obj;
						}});
		if(obj[1]!=null)
			return (List<Integer>) obj[1];
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getMostViewed() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL ALBUM_GETMOSTVIEWED();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> competitions = new ArrayList<Integer>();
							while (rs.next())
							{
								competitions.add(rs.getInt("id"));
							}
							if (competitions.size()>0)
								obj[1]=competitions;
							return obj;
						}});
		if(obj[1]!=null)
			return (List<Integer>) obj[1];
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getMostComment() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL ALBUM_GETMOSTCOMMENTED();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> competitions = new ArrayList<Integer>();
							while (rs.next())
							{
								competitions.add(rs.getInt("id"));
							}
							if (competitions.size()>0)
								obj[1]=competitions;
							return obj;
						}});
		if(obj[1]!=null)
			return (List<Integer>) obj[1];
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getByUpload(final Date start, final Date end) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL ALBUM_GETBYLASTUPLOAD(?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setDate(1, start);
						stm.setDate(2, end);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> competitions = new ArrayList<Integer>();
							while (rs.next())
							{
								competitions.add(rs.getInt("id"));
							}
							if (competitions.size()>0)
								obj[1]=competitions;
							return obj;
						}});
		if(obj[1]!=null)
			return (List<Integer>) obj[1];
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getByTag(final int tid) {
		// TODO Auto-generated method stub
		if(tid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_GETBYTAG(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, tid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								List<Integer> competitions = new ArrayList<Integer>();
								while (rs.next())
								{
									competitions.add(rs.getInt("id"));
								}
								if (competitions.size()>0)
									obj[1]=competitions;
								return obj;
							}});
			if(obj[1]!=null)
				return (List<Integer>) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getByUser(final int uid) {
		// TODO Auto-generated method stub
		if(uid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_GETBYUSER(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, uid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								List<Integer> competitions = new ArrayList<Integer>();
								while (rs.next())
								{
									competitions.add(rs.getInt("id"));
								}
								if (competitions.size()>0)
									obj[1]=competitions;
								return obj;
							}});
			if(obj[1]!=null)
				return (List<Integer>) obj[1];
		}
		return null;
	}

	@Override
	public byte[] getCover(final int aid) {
		// TODO Auto-generated method stub
		if(aid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_GETPREVIEWBIG(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, aid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("image");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}

	@Override
	public byte[] getCoverPreview(final int aid) {
		// TODO Auto-generated method stub
		if(aid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL ALBUM_GETPREVIEW(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, aid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("preview");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}

}
